/**
 * 2010(c) Copyright Oceansoft Information System Co.,LTD. All rights reserved.
 * <p>
 * Compile: JDK 1.6+
 * <p>
 * 版权所有(C)：江苏欧索软件有限公司
 * <p>
 * 公司名称：江苏欧索软件有限公司
 * <p>
 * 公司地址：中国苏州科技城青山路1号
 * <p>
 * 网址: http://www.oceansoft.com.cn
 * <p>
 * 版本: 苏州公安统一用户管理平台1.0
 * <p>
 * 作者: sunj
 * <p>
 * 文件名:ISysDeptDaoImpl.java
 * <p>
 * 类产生时间: 2012-5-31 上午16:33:25
 * <p>
 * 所在组 : 苏州公安统一用户管理平台
 * <p>
 * 所在部门: 电信/国土——技术二部
 * <p>
 * <p>
 */
package com.oceansoft.core.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.hui.platform.system.utility.StringUtil;
import com.oceansoft.core.common.constant.Constant;
import com.oceansoft.core.common.util.PageUtil;
import com.oceansoft.core.common.util.Pagination;
import com.oceansoft.core.dao.ISysDeptDao;
import com.oceansoft.core.dao.mapper.SysDeptMapper;
import com.oceansoft.core.domain.SysDept;
import com.oceansoft.core.domain.SysUser;

@Repository
public class SysDeptDaoImpl implements ISysDeptDao {

	@Resource
	private JdbcTemplate jdbcTemplate;
	
	/*@Resource(name = "extendJdbcTemplate")
	private JdbcTemplate extjt;
*/
	private boolean checkSignAdmin4Role(String roleId,SysUser user) {
		
		String SQL = "select t.*,d.name deptname from t_sys_user t,t_sys_dept d where d.deptid=t.deptid and  t.policenum in (select userid from t_sys_user_role u where u.roleid = ?) ";
		boolean flag=false;
		List<SysUser> userList =   jdbcTemplate.query(SQL,ParameterizedBeanPropertyRowMapper.newInstance(SysUser.class),roleId);//util.list(SQL, SysUser.class, roleId);
		for (SysUser sysUser : userList) {
			if(user.getName().equals(sysUser.getName())&&user.getPoliceNum().equals(sysUser.getPoliceNum())){
				flag=true;
			}
	}
		return flag;
	}
	
	@Override
	public List<SysDept> findAllDept(SysUser user) {
		String sql="";
		if("320500".equals(user.getDeptId())){
    	 sql = "SELECT T.*,1 OPEN,(SELECT  COUNT(1) FROM T_SYS_DEPT T2 WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T  WHERE (T.parentid='0'  or T.parentId='320500') order by parentid asc, deptid";
		}else{
	     sql = "SELECT T.*,1 OPEN,(SELECT  COUNT(1) FROM T_SYS_DEPT T2 WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T  WHERE (T.parentId='"+user.getDeptId()+"') order by parentid desc, deptid";
		}
		List<SysDept> list = jdbcTemplate.query(sql, new SysDeptMapper(true));
		return list;
	}

	@Override
	public List<SysDept> findAllDept(String parentId,int isparentid) { ///
		/*String sql = "SELECT T.*,1 OPEN,(SELECT COUNT(1) FROM T_SYS_DEPT T2 WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T  WHERE PARENTID=? ORDER BY ORDERNUM";
		List<SysDept> list = jdbcTemplate.query(sql, new SysDeptMapper(true), parentId);*/
		String sql = "";
		List<SysDept> list = null;
		if("320600".equals(parentId))
		{
			//sql =  "SELECT T.*,1 OPEN,1 ISPARENT FROM T_SYS_DEPT T  WHERE deptID=? union all ";
			sql += "select * from  (SELECT T.*,1 OPEN,(SELECT COUNT(1) FROM T_SYS_DEPT T2 " +
					" WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T  WHERE (PARENTID = '320600000000') or t.deptid='320600000000' ORDER BY ordernum)";
			list = jdbcTemplate.query(sql, new SysDeptMapper(true));
		}
		else
		{
			String isSelf = " ";
			if(isparentid==1){
				isSelf = " or deptid = '"+parentId+"'";
			}
			if(parentId.length()==6)
			{
				//sql =  "SELECT T.*,1 OPEN,1 ISPARENT FROM T_SYS_DEPT T  WHERE deptID=? union all ";
				//isSelf = " or deptid='" +parentId+"' ";
			}
			sql += "select * from (SELECT T.*,1 OPEN,(SELECT COUNT(1) FROM T_SYS_DEPT T2 " +
					" WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T  WHERE PARENTID=?  "+isSelf+" ORDER BY deptid)";
			if(parentId.length()==6)
			{
				list = jdbcTemplate.query(sql, new SysDeptMapper(true), parentId);
			}
			else
			{
				list = jdbcTemplate.query(sql, new SysDeptMapper(true), parentId);
			}
		}
		return list;
	}

	@Override
	public List<SysDept> findDeptByDeptId(String deptId) {
		String sql;
		if ("320500".equals(deptId)) {
			sql = "SELECT D.*,NAME as PARENTNAME FROM T_SYS_DEPT D WHERE DEPTID = ?";
		} else {
			sql = "SELECT D2.NAME AS PARENTNAME,D1.* FROM T_SYS_DEPT D2,T_SYS_DEPT D1 WHERE D1.DEPTID= ? AND D2.DEPTID=D1.PARENTID";
		}
		List<SysDept> list = jdbcTemplate.query(sql, new RowMapper<SysDept>() {

			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept sDept = new SysDept();
				sDept.setName(rs.getString("NAME"));
				sDept.setDeptId(rs.getString("DEPTID"));
				sDept.setParentId(rs.getString("PARENTID"));
				sDept.setDeep(rs.getInt("DEEP"));
				sDept.setAlias(rs.getString("ALIAS"));
				sDept.setRemark(rs.getString("REMARK"));
				sDept.setOrderNum(rs.getInt("ORDERNUM"));
				sDept.setDbId(rs.getLong("DBID"));
				//sDept.setJpin(rs.getString("JPIN"));
				//sDept.setUserTotal(rs.getInt("USER_TOTAL"));
				sDept.setParentName(rs.getString("PARENTNAME"));
				//sDept.setOverDistrictId(rs.getString("OVERDISTRICTID"));
				//sDept.setOverDistrictName(rs.getString("OVERDISTRICTNAME"));
				return sDept;
			}
		}, deptId);
		return list;
	}

	@Override
	public List<SysDept> findAllAsyncDept() {
		String sql = "SELECT T.* FROM T_SYS_DEPT T";
		List<SysDept> list = jdbcTemplate.query(sql, new SysDeptMapper(true));
		return list;
	}

	@Override
	public List<SysDept> findAllDept(final String deptId, final String path) {
		String sql;
		List<SysDept> list;
		if (Constant.ROOT_DEPT.equals(deptId)) {
			sql = "SELECT T.*,(SELECT  COUNT(1) FROM T_SYS_DEPT T2 WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T  WHERE  T.PARENTID='320500000000' or  T.DEPTID='320500000000' ORDER BY ordernum  ";
			list = jdbcTemplate.query(sql, new RowMapper<SysDept>() {
				@Override
				public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
					SysDept sDept = new SysDept();
					sDept.setDbId(rs.getLong("DBID"));
					sDept.setDeptId(rs.getString("DEPTID"));
					sDept.setName(rs.getString("NAME"));
					if ("320500".equals(rs.getString("PARENTID"))) {
						sDept.setParentId("3205");
					} else {
						sDept.setParentId(rs.getString("PARENTID"));
					}
					sDept.setDeep(rs.getInt("DEEP"));
					sDept.setAlias(rs.getString("ALIAS"));
					sDept.setRemark(rs.getString("REMARK"));
					sDept.setOrderNum(rs.getInt("ORDERNUM"));
					sDept.setIsParent(rs.getBoolean("ISPARENT"));
					//sDept.setUserTotal(rs.getInt("USER_TOTAL"));
					if (sDept.getIsParent() && sDept.getDeptId().equals(deptId)) {
						sDept.setOpen(true);
					} else {
						sDept.setOpen(false);
					}
					if (deptId.equals(sDept.getDeptId())) {
						// 根节点设置不可拖拽
						sDept.setDrag(false);
					} else {
						sDept.setDrag(true);
					}
					if (sDept.getDeep() == 0) {
						sDept.setIcon(path + "/resource/icon/userchangedept.png");
					}
					return sDept;
				}
			});
		} else {
			sql = "select t.*, (SELECT  COUNT(1) FROM T_SYS_DEPT T2 WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T WHERE deptId=? or t.parentId=? order by parentid, ordernum ";

			list = jdbcTemplate.query(sql, new RowMapper<SysDept>() {
				@Override
				public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
					SysDept sDept = new SysDept();
					sDept.setDbId(rs.getLong("DBID"));
					sDept.setDeptId(rs.getString("DEPTID"));
					sDept.setName(rs.getString("NAME"));
					sDept.setParentId(rs.getString("PARENTID"));
					sDept.setDeep(rs.getInt("DEEP"));
					sDept.setAlias(rs.getString("ALIAS"));
					sDept.setRemark(rs.getString("REMARK"));
					sDept.setOrderNum(rs.getInt("ORDERNUM"));
					sDept.setIsParent(rs.getBoolean("ISPARENT"));
					sDept.setUserTotal(rs.getInt("USER_TOTAL"));
					if (sDept.getIsParent() && sDept.getDeptId().equals(deptId)) {
						sDept.setOpen(true);
					} else {
						sDept.setOpen(false);
					}
					if (deptId.equals(sDept.getDeptId())) {
						// 根节点设置不可拖拽
						sDept.setDrag(false);
					} else {
						sDept.setDrag(true);
					}
					if (sDept.getDeep() == 0) {
						sDept.setIcon(path + "/resource/icon/userchangedept.png");
					}
					return sDept;
				}
			}, deptId, deptId);
		}
		return list;
	}

	@Override
	public List<SysDept> findAllSyncDept() {
		String sql = "SELECT * FROM T_SYS_DEPT";
		List<SysDept> list = jdbcTemplate.query(sql, new SysDeptMapper(true));
		return list;
	}

	@Override
	public List<SysUser> findUserByDeptId(String deptId) {

		String sql = "SELECT TS.NAME as DEPTNAME,TSU.* FROM T_SYS_DEPT TS ,T_SYS_USER TSU WHERE TSU.DELFLAG='0' AND TS.DEPTID=TSU.DEPTID AND TS.DEPTID LIKE "
				+ '\'' + deptId + '%' + '\'';
		List<SysUser> list = jdbcTemplate.query(sql, new RowMapper<SysUser>() {

			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser sysUser = new SysUser();
				SysDept sysDept = new SysDept();
				sysUser.setDbId(rs.getLong("DBID"));
				sysUser.setName(rs.getString("NAME"));
				sysUser.setIdCard(rs.getString("IDCARD"));
				sysUser.setDeptId(rs.getString("DEPTID"));
				sysUser.setPoliceNum(rs.getString("POLICENUM"));
				sysUser.setPassword(rs.getString("PASSWORD"));
				sysUser.setDutyName(rs.getString("DUTYNAME"));
				sysUser.setBirthday(rs.getString("BIRTHDAY"));
				sysUser.setTel(rs.getString("TEL"));
				sysUser.setRemark(rs.getString("REMARK"));
				sysDept.setName(rs.getString("DEPTNAME"));
//				sysUser.setSysDept(sysDept);
				return sysUser;
			}
		});
		return list;
	}

	@Override
	public List<SysDept> findDeptByPid(String pId) {
		String sql;
		if ("3205".equals(pId)) {
			sql = "SELECT T.*,NULL OPEN,(SELECT  COUNT(1) FROM T_SYS_DEPT T2 WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T WHERE T.PARENTID=? OR PARENTID='320500' order by parentid, ordernum";
		} else {
			sql = "SELECT T.*,NULL OPEN,(SELECT  COUNT(1) FROM T_SYS_DEPT T2 WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T WHERE T.PARENTID=? order by parentid, ordernum";
		}
		List<SysDept> list = jdbcTemplate.query(sql, new RowMapper<SysDept>() {

			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept sDept = new SysDept();

				sDept.setDeptId(rs.getString("DEPTID"));
				sDept.setName(rs.getString("NAME"));
				sDept.setParentId(rs.getString("PARENTID"));
				sDept.setDeep(rs.getInt("DEEP"));
				sDept.setAlias(rs.getString("ALIAS"));
				sDept.setRemark(rs.getString("REMARK"));
				sDept.setOpen(rs.getBoolean("OPEN"));
				sDept.setIsParent(rs.getBoolean("ISPARENT"));
				sDept.setUserTotal(rs.getInt("USER_TOTAL"));
				sDept.setDrag(true);
				return sDept;
			}
		}, pId);

		return list;
	}

	@Override
	public List<SysDept> findDeptById(String deptId) {
		String sql = "SELECT * FROM T_SYS_DEPT WHERE DEPTID= ?";
		List<SysDept> list = jdbcTemplate.query(sql, new RowMapper<SysDept>() {
			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept sysDept = new SysDept();
				sysDept.setDbId(rs.getLong("DBID"));
				sysDept.setDeptId(rs.getString("DEPTID"));
				sysDept.setName(rs.getString("NAME"));
				sysDept.setParentId(rs.getString("PARENTID"));
				sysDept.setDeep(rs.getInt("DEEP"));
				sysDept.setAlias(rs.getString("ALIAS"));
				sysDept.setOrderNum(rs.getInt("ORDERNUM"));
				sysDept.setUserTotal(rs.getInt("USER_TOTAL"));
				sysDept.setOverDistrictId(rs.getString("OVERDISTRICTID"));
				sysDept.setOverDistrictName(rs.getString("OVERDISTRICTNAME"));
				return sysDept;
			}
		}, deptId);
		return list;
	}

	@Override
	public Pagination<SysDept> findDeptByPagination(String deptId, int pageIndex) {
		String sql = "SELECT T2.NAME AS PARENTNAME,T1.DBID,T1.DEPTID,T1.NAME,t1.parentId,T1.ALIAS FROM T_SYS_DEPT T1,T_SYS_DEPT T2 WHERE T1.PARENTID = T2.DEPTID AND T1.DEPTID LIKE '%"
				+ deptId + "%' order by t1.parentId,t1.ordernum";
		String sql_list = PageUtil.getPageSQL(sql, pageIndex);
		String sql_count = PageUtil.getCountSQL(sql);
		int _total = jdbcTemplate.queryForInt(sql_count);
		List<SysDept> list = jdbcTemplate.query(sql_list, new RowMapper<SysDept>() {
			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept sysDept = new SysDept();
				sysDept.setDbId(rs.getLong("DBID"));
				sysDept.setDeptId(rs.getString("DEPTID"));
				sysDept.setName(rs.getString("NAME"));
				sysDept.setAlias(rs.getString("ALIAS"));
				sysDept.setParentName(rs.getString("PARENTNAME"));
				sysDept.setParentId(rs.getString("PARENTID"));
				sysDept.setUserTotal(rs.getInt("USER_TOTAL"));
				return sysDept;
			}
		});

		return new Pagination<SysDept>(list, _total, pageIndex);
	}

	@Override
	public Pagination<SysDept> findNameByPagination(String deptId, int pageIndex) {
		String sql = PageUtil.getPageSQL("select name from t_sys_dept where deptId=? or parentId=?", pageIndex);
		String sql_count = "SELECT COUNT(DBID) FROM T_SYS_DEPT WHERE DEPTID = ? or parentId=?";
		int _total = jdbcTemplate.queryForInt(sql_count, deptId, deptId);
		List<SysDept> list = jdbcTemplate.query(sql, new RowMapper<SysDept>() {

			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept sysDept = new SysDept();
				sysDept.setName(rs.getString("NAME"));
				return sysDept;
			}
		}, deptId, deptId);
		return new Pagination<SysDept>(list, _total, pageIndex);
	}

	@Override
	public int deleteDeptByDeptId(String deptId) {
		String sql = "DELETE FROM T_SYS_DEPT WHERE DEPTID=?";
		return jdbcTemplate.update(sql, deptId);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.oceansoft.core.dao.ISysDeptDao#fuzzyQuery(java.util.Map, int)
	 */
	@Override
	public Pagination<SysDept> fuzzyQuery(Map<String, String> params, int pageIndex) {
		StringBuilder query_sql = new StringBuilder(
				"SELECT T2.NAME as parentName,T1.* FROM T_SYS_DEPT t1,T_SYS_DEPT T2");
		query_sql.append(" WHERE T1.PARENTID = T2.DEPTID");
		StringBuilder count_sql = new StringBuilder("SELECT COUNT(*) FROM T_SYS_DEPT T1 ");
		StringBuilder sql = new StringBuilder();
		Iterator<Entry<String, String>> iter = params.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry<String, String> entry = (Map.Entry<String, String>) iter.next();
			sql.append(" and T1.").append(entry.getKey()).append(" like '%").append(entry.getValue()).append("%'");
		}
		if (sql.length() > 0) {
			count_sql.append(sql.toString().replaceFirst("and", "where"));
		}
		// �ܼ�¼��
		int _total = jdbcTemplate.queryForInt(count_sql.toString());

		// �б�
		List<SysDept> list = jdbcTemplate.query(
				PageUtil.getPageSQL(query_sql.append(sql).append(" order by t1.deptid").toString(), pageIndex),
				new RowMapper<SysDept>() {

					@Override
					public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
						SysDept sysDept = new SysDept();
						sysDept.setDbId(rs.getLong("DBID"));
						sysDept.setDeptId(rs.getString("DEPTID"));
						sysDept.setName(rs.getString("NAME"));
						sysDept.setAlias(rs.getString("ALIAS"));
						sysDept.setParentName(rs.getString("PARENTNAME"));
						sysDept.setParentId(rs.getString("PARENTID"));
						sysDept.setParentId(rs.getString("PARENTID"));
						sysDept.setUserTotal(rs.getInt("USER_TOTAL"));
						return sysDept;
					}
				});
		return new Pagination<SysDept>(list, _total, pageIndex);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.oceansoft.core.dao.ISysDeptDao#add(com.oceansoft.core.domain.SysDept)
	 */
	@Override
	public int add(SysDept dept) {
		int dbid = jdbcTemplate.queryForInt("select SEQ_SYS_DEPT.NEXTVAL from dual");
		String sql = "INSERT INTO T_SYS_DEPT (DBID,NAME,DEPTID,PARENTID,DEEP,ALIAS,ORDERNUM,JPIN,USER_TOTAL,OVERDISTRICTID,OVERDISTRICTNAME) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
		int res =  jdbcTemplate.update(sql,dbid, dept.getName(), dept.getDeptId(), dept.getParentId(), dept.getDeep(),
				dept.getAlias(), dept.getOrderNum(), dept.getJpin(), dept.getUserTotal(),dept.getOverDistrictId(),dept.getOverDistrictName());
		return res;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.oceansoft.core.dao.ISysDeptDao#saveEdit(com.oceansoft.core.domain
	 * .SysDept)
	 */
	@Override
	public int saveEdit(SysDept dept) {
		String sql = "UPDATE T_SYS_DEPT SET NAME=?,ALIAS=?,DEPTID=?,JPIN=?,USER_TOTAL = ?,OVERDISTRICTID=?,OVERDISTRICTNAME=? WHERE DBID=?";
		int res = jdbcTemplate.update(sql, dept.getName(), dept.getAlias(), dept.getDeptId(), dept.getJpin(),
				dept.getUserTotal(),dept.getOverDistrictId(),dept.getOverDistrictName(), dept.getDbId());
		return res;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.oceansoft.core.dao.ISysDeptDao#findByDbId(java.lang.String)
	 */
	@Override
	public SysDept findByDbId(long dbId) {
		String sql = "SELECT D2.NAME AS PARENTNAME,D1.* FROM T_SYS_DEPT D2,T_SYS_DEPT D1 WHERE D1.DBID= ?";
		List<SysDept> list = jdbcTemplate.query(sql, new RowMapper<SysDept>() {

			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept sysDept = new SysDept();
				sysDept.setDbId(rs.getLong("DBID"));
				sysDept.setDeptId(rs.getString("DEPTID"));
				sysDept.setName(rs.getString("NAME"));
				sysDept.setAlias(rs.getString("ALIAS"));
				sysDept.setUserTotal(rs.getInt("USER_TOTAL"));
				sysDept.setParentName(rs.getString("PARENTNAME"));
				sysDept.setOverDistrictId(rs.getString("OVERDISTRICTID"));
				sysDept.setOverDistrictName(rs.getString("OVERDISTRICTNAME"));
				return sysDept;
			}
		}, dbId);
		if (list == null || list.size() == 0)
			return null;
		return list.get(0);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.oceansoft.core.dao.ISysDeptDao#findChindrenCount(java.lang.String)
	 */
	@Override
	public int findChindrenCount(String deptId) {
		String sql = "select count(dbid) from t_sys_dept where parentid=?";
		return jdbcTemplate.queryForInt(sql, deptId);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.oceansoft.core.dao.ISysDeptDao#findParents(java.lang.String)
	 */
	@Override
	public List<String> findParents(String deptId) {
		String sql = "SELECT DEPTID FROM T_SYS_DEPT START WITH DEPTID=? CONNECT"
				+ " BY PRIOR PARENTID=DEPTID AND DEEP>=0 ORDER BY DEEP";
		List<String> deptIds = jdbcTemplate.query(sql, new RowMapper<String>() {

			@Override
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString(1);
			}
		}, deptId);
		return deptIds;
	}

	@Override
	public List<String> findParents(String deptId, int deep) {
		String sql = "SELECT DEPTID FROM T_SYS_DEPT START WITH DEPTID=? CONNECT"
				+ " BY PRIOR PARENTID=DEPTID AND DEEP>=? ORDER BY DEEP";
		List<String> deptIds = jdbcTemplate.query(sql, new RowMapper<String>() {

			@Override
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString(1);
			}
		}, deptId, deep);
		return deptIds;
	}

	@Override
	public List<String> getDeptId(String parentId) {
		String sql = "select deptId,deep from t_sys_dept where parentId = ? order by deptId for update ";
		List<String> deptIds = jdbcTemplate.query(sql, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString(1).substring(rs.getInt(2) * 2 + 2, rs.getInt(2) * 2 + 4);
			}
		}, parentId);
		return deptIds;
	}

	@Override
	public void updateOrderNums(int orderNum, int targetNum, String deptId) {
		String sql;
		if (orderNum < targetNum) {
			sql = "update t_sys_dept set ordernum = ordernum-1 where ordernum>? and ordernum<=? and parentid= (select parentid from t_sys_dept where deptid=?)";
		} else {
			sql = "update t_sys_dept set ordernum = ordernum+1 where ordernum<? and ordernum>? and parentid= (select parentid from t_sys_dept where deptid=?)";
		}
		jdbcTemplate.update(sql, orderNum, targetNum, deptId);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.oceansoft.core.dao.ISysDeptDao#updateOrderNum(int,
	 * java.lang.String)
	 */
	@Override
	public void updateOrderNum(int targetNum, String deptId) {
		String sql = "UPDATE T_SYS_DEPT SET ORDERNUM = ? WHERE DEPTID = ?";
		jdbcTemplate.update(sql, targetNum, deptId);
	}

	@Override
	public List<SysDept> findDeptsByAppId(String appId) {
		String sql = "SELECT T.*,NULL OPEN,(SELECT  COUNT(1) FROM T_SYS_DEPT T2 WHERE T2.PARENTID = T.DEPTID)  ISPARENT from t_app_dept t where t.appid=?";
		List<SysDept> depts = jdbcTemplate.query(sql, new RowMapper<SysDept>() {

			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept dept = new SysDept();
				dept.setDeptId(rs.getString("DEPTID"));
				dept.setName(rs.getString("NAME"));
				dept.setParentId(rs.getString("PARENTID"));
				dept.setAlias(rs.getString("ALIAS"));
				dept.setRemark(rs.getString("REMARK"));
				dept.setDeep(rs.getInt("DEEP"));
				dept.setOrderNum(rs.getInt("ORDERNUM"));
				dept.setOpen(rs.getBoolean("OPEN"));
				dept.setIsParent((rs.getBoolean("ISPARENT")));
				dept.setUserTotal(rs.getInt("USER_TOTAL"));
				return dept;
			}

		}, appId);
		return depts;
	}

	@Override
	public void updateChildren(String oldParent, String newParent) {
		String sql = "update t_sys_dept set parentId = ? where parentid=?";
		jdbcTemplate.update(sql, newParent, oldParent);
	}

	@Override
	public List<SysDept> findChildren(String deptId) {
		String sql = "SELECT * FROM T_SYS_DEPT START WITH PARENTID=? CONNECT BY PRIOR DEPTID = PARENTID";
		List<SysDept> depts = jdbcTemplate.query(sql, new RowMapper<SysDept>() {
			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept dept = new SysDept();
				dept.setDeptId(rs.getString("DEPTID"));
				dept.setName(rs.getString("NAME"));
				dept.setParentId(rs.getString("PARENTID"));
				dept.setAlias(rs.getString("ALIAS"));
				dept.setRemark(rs.getString("REMARK"));
				dept.setDeep(rs.getInt("DEEP"));
				dept.setOrderNum(rs.getInt("ORDERNUM"));
				dept.setDbId(rs.getInt("DBID"));
				dept.setUserTotal(rs.getInt("USER_TOTAL"));
				return dept;
			}
		}, deptId);
		return depts;
	}

	@Override
	public List<String> findChildrenId(String deptId) {
		String sql = "select deptId from t_sys_dept start with parentid=? connect by prior deptId = parentId";
		List<String> depts = jdbcTemplate.query(sql, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString(1);
			}
		}, deptId);
		return depts;
	}

	@Override
	public List<SysDept> findParentDepts(String deptId) {
		String sql = "select * from t_sys_dept start with deptId=? connect  by prior parentid=deptid and deep>=0 order by deep";
		List<SysDept> depts = jdbcTemplate.query(sql, new RowMapper<SysDept>() {
			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept dept = new SysDept();
				dept.setDeptId(rs.getString("DEPTID"));
				dept.setName(rs.getString("NAME"));
				dept.setParentId(rs.getString("PARENTID"));
				dept.setAlias(rs.getString("ALIAS"));
				dept.setRemark(rs.getString("REMARK"));
				dept.setDeep(rs.getInt("DEEP"));
				dept.setOrderNum(rs.getInt("ORDERNUM"));
				dept.setDbId(rs.getInt("DBID"));
				dept.setUserTotal(rs.getInt("USER_TOTAL"));
				return dept;
			}
		}, deptId);
		return depts;
	}

	@Override
	public List<SysDept> findDeptInfoByDeptId(String deptId) {
		String sql = "SELECT * FROM T_SYS_DEPT WHERE DEPTID=?";
		List<SysDept> list = jdbcTemplate.query(sql, new RowMapper<SysDept>() {
			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept dept = new SysDept();
				dept.setDeptId(rs.getString("DEPTID"));
				dept.setName(rs.getString("NAME"));
				dept.setParentId(rs.getString("PARENTID"));
				dept.setAlias(rs.getString("ALIAS"));
				dept.setRemark(rs.getString("REMARK"));
				dept.setDeep(rs.getInt("DEEP"));
				dept.setOrderNum(rs.getInt("ORDERNUM"));
				dept.setDbId(rs.getInt("DBID"));
				dept.setUserTotal(rs.getInt("USER_TOTAL"));
				return dept;
			}
		}, deptId);
		return list;
	}

	@Override
	public int getOrderNumber(String parentId) {
		String sql = "SELECT MAX(ORDERNUM) FROM T_SYS_DEPT WHERE PARENTID =?";
		return jdbcTemplate.queryForInt(sql, parentId);
	}

	public List<SysDept> findContactDepts(final String iconPath) {
		String sql = "SELECT T.*,1 OPEN,(SELECT  COUNT(1) FROM T_SYS_DEPT T2 WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T  WHERE T.DEPTID='320500' OR T.PARENTID='320500' ORDER BY PARENTID DESC, ORDERNUM";
		List<SysDept> list = jdbcTemplate.query(sql, new RowMapper<SysDept>() {
			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept sDept = new SysDept();
				sDept.setDbId(rs.getLong("DBID"));
				sDept.setDeptId(rs.getString("DEPTID"));
				sDept.setName(rs.getString("NAME"));
				sDept.setParentId(rs.getString("PARENTID"));
				sDept.setDeep(rs.getInt("DEEP"));
				sDept.setAlias(rs.getString("ALIAS"));
				sDept.setRemark(rs.getString("REMARK"));
				sDept.setOrderNum(rs.getInt("ORDERNUM"));
				sDept.setOpen(rs.getBoolean("OPEN"));
				sDept.setUserTotal(rs.getInt("USER_TOTAL"));
				sDept.setOverDistrictId(rs.getString("OVERDISTRICTID"));
				sDept.setOverDistrictName(rs.getString("OVERDISTRICTNAME"));
				sDept.setIsParent(rs.getBoolean("ISPARENT"));
				if (0 == sDept.getDeep()) {
					sDept.setIcon(iconPath);
				}
				return sDept;
			}
		});
		return list;
	}

	@Override
	public int findCountByNameAlias(SysDept dept) {
		if (dept.getDbId() != 0) {
			// 编辑
			String sql = "select count(*) from t_sys_dept where (name=? or alias=?) and parentId=? and dbId !=?";
			return jdbcTemplate.queryForInt(sql, dept.getName(), dept.getAlias(), dept.getParentId(), dept.getDbId());
		}
		String sql = "select count(*) from t_sys_dept where name=? or alias=? and parentId=?";
		return jdbcTemplate.queryForInt(sql, dept.getName(), dept.getAlias(), dept.getParentId());

	}

	@Override
	public List<SysDept> findByJpin(String jpin) {
		String sql = "select deptId,alias from t_sys_dept where (jpin like '" + jpin + "%' or alias like '%" + jpin
				+ "%' or INSTR('"+jpin+"',alias)>0) and (parentid='320500' or parentid ='0') order by ordernum";
		System.out.println("==="+sql);
		return jdbcTemplate.query(sql, new RowMapper<SysDept>() {

			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept dept = new SysDept();
				dept.setDeptId(rs.getString("deptId"));
				dept.setName(rs.getString("alias"));
				return dept;
			}

		});
	}
        
        @Override
         public String findDeptByAlias(String alias){
             String sql = "select deptId from t_sys_dept where alias = ?";
             List<String> list = jdbcTemplate.query(sql, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString(1);
			}
		}, alias);
             if(list.size()==0){
                 return "000000000000";
             }
             return list.get(0);
         }

		@Override
		public List<SysDept> updateOrganizationInfo() {
			return null;
			/*
			StringBuffer sql = new StringBuffer("select l.codeitemid deptId,l.codeitemname alias,l.parent parentId,l.child deep,count(1) userTotal");
            sql.append(" from VIEW_A01 t right join view_organization l");
            sql.append(" on t.b0001=l.codeitemid where (l.parent='3205' or l.parent='320500' ) and l.codeitemid !='3205'");
            sql.append(" group by codeitemid,l.codeitemname,l.parent,l.child");
            return extjt.query(sql.toString(), ParameterizedBeanPropertyRowMapper.newInstance(SysDept.class));
		*/
		}

		@Override
		public void updateUserTotal(List<SysDept> list) {//seq_sys_dept.nextval
			    /*String sql="insert into t_tmp_dept(dbid,DEPTID,ALIAS,PARENTID,DEEP,USER_TOTAL) " +
					"values(seq_sys_dept.nextval,?,?,?,?,?)";*/
			String _sql="update t_tmp_dept set USER_TOTAL=? where DEPTID=?";
			if(null!=list){
				for(int i=0;i<list.size();i++){
					if(null!=list.get(i).getDeptId()&&!"".equals(list.get(i).getDeptId())){
					//查询单位人员总数有没变动
						String sql_="select t.USER_TOTAL from t_tmp_dept t where DEPTID="+list.get(i).getDeptId();
						List<SysDept> listcheck=jdbcTemplate.query(sql_, new RowMapper<SysDept>(){

							@Override
							public SysDept mapRow(ResultSet rs, int arg1)throws SQLException {
								SysDept lh= new SysDept();
								lh.setUserTotal(rs.getInt("USER_TOTAL"));
								return lh;
							}
						});
					if(null!=listcheck&&null!=listcheck.get(0).getUserTotal()&&!"".equals(listcheck.get(0).getUserTotal())){
						    if(listcheck.get(0).getUserTotal()!=list.get(i).getUserTotal()){
						       int j=jdbcTemplate.update(_sql, list.get(i).getUserTotal(),list.get(i).getDeptId());
							if(j>0){
								System.out.println("更新一条记录...");
							 }}
						    else{
						    	System.out.println("无需更改...");
						    }
					}
					}
				}
			}
		}

		@Override
		public List<SysDept> findByDw1AndDw2(String dw1, String dw2) {
			String sql="select t.code,t.name from t_rsxt_dept t  where t.code like '"+dw1+"%' and t.name like '%"+dw2+"%'";
			return jdbcTemplate.query(sql, new RowMapper<SysDept>() {

				@Override
				public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
					SysDept dept = new SysDept();
					dept.setDeptId(rs.getString("code"));
					dept.setName(rs.getString("name"));
					return dept;
				}

			});
		}

		@Override
		public SysDept selDeptInfo(String dw1) {
			String sql ="select DEPTID,ALIAS from t_sys_dept where parentid='320500' and DEPTID='"+dw1+"'";
			List<SysDept> list = jdbcTemplate.query(sql.toString(), ParameterizedBeanPropertyRowMapper.newInstance(SysDept.class));
			if(list.isEmpty())
			{
				return null;
			}
			return list.get(0);
		}

		@Override
		public List<SysDept> findAllSubDept(String deptId) {///
			
			deptId = StringUtil.fillPrefix(deptId, 12,"0", false);
			
			String sql = "SELECT T.*,1 OPEN,(SELECT  COUNT(1) FROM T_SYS_DEPT T2 WHERE T2.PARENTID = T.DEPTID)  ISPARENT FROM T_SYS_DEPT T  WHERE (T.deptid='"+deptId+"'  or T.parentId='"+deptId+"') order by parentid desc, ordernum desc";
			List<SysDept> list = jdbcTemplate.query(sql, new SysDeptMapper(true));
			return list;
		}

		@Override
		public List<SysDept> _findByJpin(String jpin) {
			String sql = "select deptId,alias from t_sys_dept where (jpin like '" + jpin + "%' or alias like '%" + jpin
					+ "%' or INSTR('"+jpin+"',alias)>0) order by ordernum";
			System.out.println("==="+sql);
			return jdbcTemplate.query(sql, new RowMapper<SysDept>() {

				@Override
				public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
					SysDept dept = new SysDept();
					dept.setDeptId(rs.getString("deptId"));
					dept.setName(rs.getString("alias"));
					return dept;
				}

			});
		}

	@Override
	public int finddeptcount(String parentId) {
		String sql ="select count(1) from t_sys_dept where parentid =rpad('"+parentId+"',12,'0')";
		return jdbcTemplate.queryForInt(sql);
	}

	@Override
	public SysDept findByParentAndName(String deptId, String name) {
		String sql="select name ,deptid from t_sys_dept where parentid =rpad('"+deptId+"',12,'0')" +" and name ='"+name+"'";
		System.out.println("==="+sql);
		List<SysDept> list= jdbcTemplate.query(sql, new RowMapper<SysDept>() {

			@Override
			public SysDept mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDept dept = new SysDept();
				dept.setDeptId(rs.getString("deptId"));
				dept.setName(rs.getString("name"));
				return dept;
			}
		});
		if(list.size()>0){
			return list.get(0);
		}
		return new SysDept();
	}

}
